using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.ComponentModel;
using System.ComponentModel.Design;
using System.ComponentModel.Design.Serialization;
using System.Diagnostics;
using System.Drawing;
using System.Drawing.Design;
using System.Windows.Forms;
using System.Windows.Forms.Design;
using System.Workflow.Activities;
using System.Workflow.Activities.Rules;
using System.Workflow.ComponentModel;
using System.Workflow.ComponentModel.Compiler;
using System.Workflow.ComponentModel.Design;
using System.Workflow.ComponentModel.Serialization;
using System.Workflow.Runtime;
using Citrix.WorkflowStudio.Common;
using Citrix.WorkflowStudio.CustomActivities.Designers;
using Citrix.WorkflowStudio.CustomActivities.Editors;
using Citrix.WorkflowStudio.CustomActivities.Serializers;
using Citrix.WorkflowStudio.User;
using System.Data.SqlClient;

namespace Citrix.WorkflowStudio.Activities.SQLActivityLibrary
{
	#region Attribute Definitions and Comments
	// [Designer(typeof(BaseActivityDesigner))]
	//     Defines what activity designer will be used for the current activity.
	//     The "BaseActivityDesigner" is the standard Citrix designer.
	// [DisplayNameAttribute(@"MyActivity")]
	//     Allows you to specify the name for your activity.
	//     This is the name that will appear in the toolbox, and the same name that
	//     will appear on the activity in the design surface.
	// [Description(@"This activity <...>.")]
	//     Allows you to provide a brief text description of the activity.
	//     This text will appear at the bottom of the toolbox in the Designer console
	//     when the activity is selected in the tree view and in the tooltip when
	//     the user hovers over the activity on the design surface. Typically, this 
	//     text is short and simply gives a high-level overiew of what the task does.
	// [ActivityTreePath(@"Folder/SubFolder")]
	//     Allows you to specify the name to use for this activity in the toolbox.
	//     NOTE: Use forward slashes not backward slashes to denote nested folders. 
	// [ActivityValidator(typeof(MyActivityValidator))]
	//     Defines which class is responsible for the validation logic of the activity.
	//     Typically each activity has its own dedicated validator class.
	// [BaseActivityDesigner(ImageResourceName = @"Resources.$safeclassname$32.png", AssemblyType = typeof($safeclassname$))]
	//     Allows you to specify any one, or multiple, of the following attributes
	//     to modify the look of activities when placed on the design surface
	//         ImageResourceName        GlowIntensity        TextForeColor         TextFontSize
	//         ShineColor               BackColor            ShapeRadius           Width
	//         GlowColor                MaskColor            TextFontFamily        Height
	//     Typically the ImageResourceName is used to provide an image to replace the default.
	//     The AssemblyType is required and must be set to the type of the Activity class.
	// [ToolboxBitmapAttribute(typeof($safeclassname$), @"Resources.$safeclassname$16.png")]
	//     Allows you to specify the 16x16 image file to use as the icon for the activity in the toolbox.
	// [DesignerSerializer(typeof(BindingListSerializer), typeof(WorkflowMarkupSerializer))]
	//     If you have any BindingList<> properties in this activity your activity needs to declare
	//     a DesignerSerializer attribute calling the Citrix BindingListSerializer.
	// [ActivityReferenceAssemblies(new string[] { "$snapinname$, Version=1.0.0.0, Culture=neutral, PublicKeyToken=12345ABCD6789EFG" } )]
	//     Allows you to specify one or more additional assemblies that assemblies that you want
	//     referenced by a project when the activity is dropped on the design surface.
	//     Rreferences that are added to the activity project should be added here as well.

	[Designer(typeof(BaseActivityDesigner))]
	[DisplayNameAttribute(@"SQLExecute")]
	[Description(@"This activity is used to execute Insert, Update, and Delete commands on a SQL Server database.")]
	[ActivityTreePath(@"SQL Server")]
	[ActivityValidator(typeof(SQLExecuteValidator))]
	[ToolboxBitmapAttribute(typeof(SQLExecute), @"Resources.SQLExecute16.png")]
	[BaseActivityDesigner(ImageResourceName = @"Resources.SQLExecute32.png", AssemblyType = typeof(SQLExecute))]
	#endregion Attribute Definitions and Comments

	public partial class SQLExecute : ActivityBase
	{
		#region Constructor
		public SQLExecute()
		{
			InitializeComponent();

			// Define default values for properties within this activity
            this.DatabaseServer = "";
            this.DatabaseName = "";
            this.SQLUsername = "";
            this.SQLPassword = "";
            this.SQLCommand = "";
            this.Output = "";
		}
		#endregion Constructor

		// Dependency Property definition section
		#region Dependency Properties

        #region DatabaseServer Property
        public static DependencyProperty DatabaseServerProperty = DependencyProperty.Register("DatabaseServer", typeof(String), typeof(SQLExecute));
        [DefaultValue("")]
        [Category(@"Parameters")]
        [DisplayName(@"Database Server")]
        [Description(@"Specify the name of the server hosting the database.")]
        [Browsable(true)]
        [EditorAttribute(typeof(TextEditor), typeof(UITypeEditor))]
        public String DatabaseServer
        {
            get
            {
                return ((String)(base.GetValue(DatabaseServerProperty)));
            }
            set
            {
                base.SetValue(DatabaseServerProperty, value);
            }
        }
        #endregion DatabaseServer Property

        #region DatabaseName Property
        public static DependencyProperty DatabaseNameProperty = DependencyProperty.Register("DatabaseName", typeof(String), typeof(SQLExecute));
        [DefaultValue("")]
        [Category(@"Parameters")]
        [DisplayName(@"Database Name")]
        [Description(@"Specify the name of the database to connect to.")]
        [Browsable(true)]
        [EditorAttribute(typeof(TextEditor), typeof(UITypeEditor))]
        public String DatabaseName
        {
            get
            {
                return ((String)(base.GetValue(DatabaseNameProperty)));
            }
            set
            {
                base.SetValue(DatabaseNameProperty, value);
            }
        }
        #endregion DatabaseName Property

        #region SQLUsername Property
        public static DependencyProperty SQLUsernameProperty = DependencyProperty.Register("SQLUsername", typeof(String), typeof(SQLExecute));
        [DefaultValue("")]
        [Category(@"Parameters")]
        [DisplayName(@"SQL Username")]
        [Description(@"Specify your SQL Server username for connecting to the database.")]
        [Browsable(true)]
        [EditorAttribute(typeof(TextEditor), typeof(UITypeEditor))]
        public String SQLUsername
        {
            get
            {
                return ((String)(base.GetValue(SQLUsernameProperty)));
            }
            set
            {
                base.SetValue(SQLUsernameProperty, value);
            }
        }
        #endregion SQLUsername Property

        #region SQLPassword Property
        public static DependencyProperty SQLPasswordProperty = DependencyProperty.Register("SQLPassword", typeof(String), typeof(SQLExecute));
        [Category(@"Parameters")]
        [DisplayName(@"SQL Password")]
        [Description(@"Specify your SQL Server password for connecting to the database.")]
        [Browsable(true)]
        [EditorAttribute(typeof(PasswordEditor), typeof(UITypeEditor))]
        [BindTypes(new Type[] { typeof(String) })]
        public object SQLPassword
        {
            get
            {
                if (HiddenSecretKey != null && HiddenSecretKey.encryptionType != CTXPasswordEncryptionType.NotSet)
                    return HiddenSecretKey.ToString();
                else
                    return base.GetValue(SQLExecute.SQLPasswordProperty);
            }
            set
            {
                if (value != null)
                {
                    EncryptedPasswordInfo info = new EncryptedPasswordInfo();
                    if (info.SetData(value.ToString()))
                    {
                        HiddenSecretKey = info;
                    }
                }
            }
        }
        [Browsable(false)]
        [DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
        public EncryptedPasswordInfo HiddenSecretKey
        {
            get
            {
                EncryptedPasswordInfo info = new EncryptedPasswordInfo();
                string val = (string)base.GetValue(SQLExecute.SQLPasswordProperty);
                if (val != null) 
                    info.SetData(val);
                return info;
            }
            set
            {
                if (value != null)
                {
                    base.SetValue(SQLExecute.SQLPasswordProperty, value.GetString());
                }
                else
                    base.SetValue(SQLExecute.SQLPasswordProperty, null);
            }
        }
        [Browsable(false)]
        [DesignerSerializationVisibility(DesignerSerializationVisibility.Visible)]
        public string SecretKeyData
        {
            get
            {
                if (HiddenSecretKey != null)
                    return HiddenSecretKey.GetString();
                else
                    return null;
            }
            set
            {
                EncryptedPasswordInfo info = new EncryptedPasswordInfo();
                if (info.SetData(value) && info.encryptionType != CTXPasswordEncryptionType.NotSet)
                {
                    HiddenSecretKey = info;
                }
            }
        }
        #endregion SQLPassword Property

        #region SQLCommand Property
        public static DependencyProperty SQLCommandProperty = DependencyProperty.Register("SQLCommand", typeof(String), typeof(SQLExecute));
        [DefaultValue("")]
        [Category(@"Parameters")]
        [DisplayName(@"SQL Command")]
        [Description(@"Specify the SQL command to execute on the database.  Insert, Update, and Delete commands can be executed with this activity.")]
        [Browsable(true)]
        [EditorAttribute(typeof(TextEditor), typeof(UITypeEditor))]
        public String SQLCommand
        {
            get
            {
                return ((String)(base.GetValue(SQLCommandProperty)));
            }
            set
            {
                base.SetValue(SQLCommandProperty, value);
            }
        }
        #endregion SQLCommand Property

        #region Output
        public static DependencyProperty OutputProperty = DependencyProperty.Register("Output", typeof(String), typeof(SQLExecute));
        [Category(@"Read-Only Output")]
        [DisplayName(@"Output")]
        [Description(@"Number of records affected by the SQL command that was executed")]
        [ReadOnly(true)]
        [OutputAttribute]
        [ShowInWFSEditors]
        [BindTypes(new Type[] { typeof(String), typeof(Int32) })]
        public String Output
        {
            get
            {
                return ((String)(base.GetValue(OutputProperty)));
            }
            set
            {
                base.SetValue(OutputProperty, value);
            }
        }
        #endregion Output
        
		#endregion Dependency Properties

		// The logic for your activity belongs in the Execute method below
		#region Activity Execution Logic
		protected override ActivityExecutionStatus Execute(ActivityExecutionContext executionContext)
		{
			// Expand all string properties with the required attribute
			ExpandStringProperties(executionContext.Activity);

			try
			{
                //Declare local variables
                SqlConnection l_objConnection = new SqlConnection();
                SqlCommand l_objCommand = new SqlCommand();
                int l_intReturn = 0;

                //Define SQL connection string
                l_objConnection.ConnectionString = "Server=" + this.DatabaseServer.Trim() + ";"
                                                 + "Database=" + this.DatabaseName.Trim() + ";"
                                                 + "Uid=" + this.SQLUsername.Trim() + ";"
                                                 + "Pwd=" + GlobalUtilities.GetPasswordValue(this.SQLPassword, this.HiddenSecretKey).Trim() + ";";

                //Open the SQL connection
                l_objConnection.Open();

                //Assign the SQL connection to the Command object
                l_objCommand.Connection = l_objConnection;

                //Set command text
                l_objCommand.CommandText = this.SQLCommand.Trim();

                //Execute query
                l_intReturn = l_objCommand.ExecuteNonQuery();

                //Return number of records affected by the query
                Output = l_intReturn.ToString();

                //Close the SQL connection
                if (l_objConnection.State == System.Data.ConnectionState.Open)
                {
                    l_objConnection.Close();
                }

                //Release memory
                l_objConnection = null;
                l_objCommand = null;
			}
			catch (Exception e)
			{
				String errmsg = "An error has occurred. The error is \"" + e.Message.ToString() + "\"";

				if (e.InnerException != null && e.InnerException.ToString().Length > 0)
					errmsg += "\nAdditional error info: \"" + e.InnerException.ToString() + "\"";

				throw new Exception(errmsg);

				// NOTE: Do not set ActivityExecutionStatus to Faulting if you throw an exception manually.
			}

			// You need to conclude your Execute method by setting the ActivityExecutionStatus.
			// Valid values are:
			//	Canceling
			//	Closed
			//	Compensating
			//	Executing
			//	Faulting
			//	Initialized
			return ActivityExecutionStatus.Closed;
		}
		#endregion Activity Execution Logic
	}

	// Property validation code
	//     In the Validate method you have to create a new instance of the activity class above,
	//     create a new ValidationErrorCollection instance, and then write the code to process the validation.
	//     For each validation error/warning, you need to add the error/warning to the ValidationErrorCollection.
	//     Finally, the Validate method should return the ValidationErrorCollection object.
	#region Validation Logic
	public class SQLExecuteValidator : ActivityValidator
	{
		public override ValidationErrorCollection Validate(ValidationManager manager, object obj)
		{
			SQLExecute MyActivity = obj as SQLExecute;
			ValidationErrorCollection errs = new ValidationErrorCollection();
            
			if ((MyActivity != null) && (MyActivity.Parent != null))
			{
    			// You can either set validation warnings or errors.
	        	//     To set a warning, pass 'true' for the 3rd parameter to errs.Add.
                //     To set an error, pass 'false' for the 3rd parameter to errs.Add.
                if (!GlobalUtilities.StringValidates(MyActivity, MyActivity.SQLUsername, SQLExecute.SQLUsernameProperty))
                    errs.Add(new ValidationError(@"You must specify a value for the 'SQL Username' property.", 101, false, @"SQLUsername"));

                if (!GlobalUtilities.PasswordValidates(MyActivity, MyActivity.HiddenSecretKey, SQLExecute.SQLPasswordProperty))
                    errs.Add(new ValidationError(@"You must specify a value for the 'SQL Password' property.", 101, false, @"SQLPassword"));

                if (!GlobalUtilities.StringValidates(MyActivity, MyActivity.DatabaseServer, SQLExecute.DatabaseServerProperty))
                    errs.Add(new ValidationError(@"You must specify a value for the 'Database Server' property.", 101, false, @"DatabaseServer"));

                if (!GlobalUtilities.StringValidates(MyActivity, MyActivity.DatabaseName, SQLExecute.DatabaseNameProperty))
                    errs.Add(new ValidationError(@"You must specify a value for the 'Database Name' property.", 101, false, @"DatabaseName"));

                if (!GlobalUtilities.StringValidates(MyActivity, MyActivity.SQLCommand, SQLExecute.SQLCommandProperty))
                    errs.Add(new ValidationError(@"You must specify a value for the 'SQL Command' property.", 101, false, @"SQLCommand"));
            }
            
			#region StopOnError Validation
        	        if (!MyActivity.StopOnError)
	                {
        	            Boolean found = false;
	                    if (MyActivity.Activities != null)
        	            {
                	        for (int i = 0; ((i < MyActivity.Activities.Count) && (!found)); i++)
                        	{
	                            if (MyActivity.Activities[i].GetType() == typeof(System.Workflow.ComponentModel.FaultHandlersActivity))
        	                    {
                	                found = true;
                        	        CompositeActivity faultHandlers = (CompositeActivity)(MyActivity.Activities[i]);
                                	if ((faultHandlers.Activities == null) || (faultHandlers.Activities.Count == 0))
	                                    errs.Add(new ValidationError("The 'Stop On Error' property is set to 'false' but the auto-generated faultHandlerActivity has been manually deleted.", 101, false, "StopOnError"));
        	                    }
                	        }
                        	if (!found)
	                            errs.Add(new ValidationError("The 'Stop On Error' property is set to 'false' but the auto-generated faultHandlersActivity has been manually deleted.", 101, false, "StopOnError"));
        	            }
                	}
			#endregion StopOnError Validation

            
			// Return the ValidationErrorCollection
			return errs;
		}
	}
	#endregion Validation Logic
}
